//
//  DBProcessing.m
//  Project6
//
//  Created by ToanDK on 12/30/12.
//  Copyright (c) 2012 DungNP. All rights reserved.
//

#import "DBProcessing.h"

@implementation DBProcessing

+(void) checkAndCreateDatabase:(NSString *) databasePath dataBaseName:(NSString *) databaseName
{
    // Check if the SQL database has already been saved to the users phone, if not then copy it over
	BOOL success;
    
	// Create a FileManager object, we will use this to check the status
	// of the database and to copy it over if required
	NSFileManager *fileManager = [NSFileManager defaultManager];
    
	// Check if the database has already been created in the users filesystem
	success = [fileManager fileExistsAtPath:databasePath];
    
	// If the database already exists then return without doing anything
	if(success) return;
    
	// If not then proceed to copy the database from the application to the users filesystem
    
	// Get the path to the database in the application package
	NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:databaseName];
    
	// Copy the database from the package to the users filesystem
	[fileManager copyItemAtPath:databasePathFromApp toPath:databasePath error:nil];
    
	
}

// add newreport record
+(void) addNewreport:(NSMutableDictionary *) newreport onDataBase:(NSString *)databasePath
{

    sqlite3 *contactDB;
    sqlite3_stmt    *statement;
    const char *dbpath = [databasePath UTF8String];

    
    if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK)
    {
        NSString *insertSQL = [NSString stringWithFormat:
                               @"INSERT INTO tblNewreport (api, udid, wfid,wtgid,wtgvisid,blade,access,side) VALUES (\"%@\", \"%@\", \"%@\",\"%@\", \"%@\", \"%@\", \"%@\", \"%@\")", (NSString*)[newreport objectForKey:@"api"],(NSString*)[newreport objectForKey:@"udid"],(NSString*)[newreport objectForKey:@"wfid"],(NSString*)[newreport objectForKey:@"wtgid"],(NSString*)[newreport objectForKey:@"wtgvisid"],(NSString*)[newreport objectForKey:@"blade"],(NSString*)[newreport objectForKey:@"access"],(NSString*)[newreport objectForKey:@"side"]];
        
        const char *insert_stmt = [insertSQL UTF8String];
        
        sqlite3_prepare_v2(contactDB, insert_stmt, -1, &statement, NULL);
        if (sqlite3_step(statement) == SQLITE_DONE)
        {
            NSLog(@"sussess to add newreport");
        } else {
            NSLog(@"Failed to add newreport");
        }
        sqlite3_finalize(statement);
        sqlite3_close(contactDB);
    }
    
       
}

/// add new damage record to database
+(void) addDamagereport:(NSMutableDictionary *) damagereport withNewreportID:(NSString *) newreportid onDataBase:(NSString *)databasePath
{
    NSString * distance = [damagereport objectForKey:@"distance"];
    NSString * surface  =  [damagereport objectForKey:@"surface"];
    NSString * damage = [damagereport objectForKey:@"damage"];
    NSString * cause =[damagereport objectForKey:@"cause"];
    NSString * length = [damagereport objectForKey:@"length"];
    NSString * width =[damagereport objectForKey:@"width"];
    NSString * area =[damagereport objectForKey:@"area"];
    NSString * depth = [damagereport objectForKey:@"depth"];
    NSString * rating = [damagereport objectForKey:@"rating"];
    NSString * notes = [damagereport objectForKey:@"notes"];
    
    NSString * photo1 =@"";
    if([damagereport objectForKey:@"photo1"])
        photo1 =[damagereport objectForKey:@"photo1"];
    
    NSString * photo2 =@"";
    if( [damagereport objectForKey:@"photo2"])
        photo2 = [damagereport objectForKey:@"photo2"];
    
    NSString * photo3 = @"";
    if([damagereport objectForKey:@"photo3"])
        photo3 = [damagereport objectForKey:@"photo3"];
    
    NSString * photo4 = @"";
    if([damagereport objectForKey:@"photo4"])
        photo4 = [damagereport objectForKey:@"photo4"];
    
    sqlite3 *contactDB;
    sqlite3_stmt    *statement;
    const char *dbpath = [databasePath UTF8String];
    
    
    if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK)
    {
        NSString *insertSQL = [NSString stringWithFormat:
                               @"INSERT INTO tblDamageReport (newreportid, distance, surface,damage,cause,length,width,area,depth,rating,notes,photo1,photo2,photo3,photo4) VALUES (\"%@\", \"%@\", \"%@\",\"%@\", \"%@\", \"%@\", \"%@\", \"%@\",\"%@\",\"%@\", \"%@\", \"%@\", \"%@\", \"%@\",\"%@\")",newreportid,
                                  distance,surface,damage,cause,length,width,area,depth,rating,notes,photo1,photo2,photo3,photo4];
        
        const char *insert_stmt = [insertSQL UTF8String];
        
        sqlite3_prepare_v2(contactDB, insert_stmt, -1, &statement, NULL);
        if (sqlite3_step(statement) == SQLITE_DONE)
        {
            NSLog(@"sussess to add damage report");
        } else {
            NSLog(@"Failed to add damage report");
        }
        sqlite3_finalize(statement);
        sqlite3_close(contactDB);
    }
}

/// update damage record to database
+(void) updateDamagereport:(NSMutableDictionary *) damagereport withNewreportID:(NSString *) newreportid onDataBase:(NSString *)databasePath
{
    NSString * distance = [damagereport objectForKey:@"distance"];
    NSString * surface  =  [damagereport objectForKey:@"surface"];
    NSString * damage = [damagereport objectForKey:@"damage"];
    NSString * cause =[damagereport objectForKey:@"cause"];
    NSString * length = [damagereport objectForKey:@"length"];
    NSString * width =[damagereport objectForKey:@"width"];
    NSString * area =[damagereport objectForKey:@"area"];
    NSString * depth = [damagereport objectForKey:@"depth"];
    NSString * rating = [damagereport objectForKey:@"rating"];
    NSString * notes = [damagereport objectForKey:@"notes"];
    
    NSString * photo1 =@"";
    if([damagereport objectForKey:@"photo1"])
        photo1 =[damagereport objectForKey:@"photo1"];
    
    NSString * photo2 =@"";
    if( [damagereport objectForKey:@"photo2"])
        photo2 = [damagereport objectForKey:@"photo2"];
    
    NSString * photo3 = @"";
    if([damagereport objectForKey:@"photo3"])
        photo3 = [damagereport objectForKey:@"photo3"];
    
    NSString * photo4 = @"";
    if([damagereport objectForKey:@"photo4"])
        photo4 = [damagereport objectForKey:@"photo4"];
    
    sqlite3 *contactDB;
    sqlite3_stmt    *statement;
    const char *dbpath = [databasePath UTF8String];
    
    
    if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK)
    {
        NSString *updateSQL = [NSString stringWithFormat:
                               @"UPDATE tblDamageReport SET distance =%@,surface =%@,damage=%@,cause=%@,length=%@,width=%@,area=%@,depth=%@,rating=%@,notes=%@,photo1=%@,photo2=%@,photo3=%@,photo4=%@ WHERE newreportid=%@",distance,surface,damage,cause,length,width,area,depth,rating,notes,photo1,photo2,photo3,photo4,newreportid];
        
        const char *update_stmt = [updateSQL UTF8String];
        
        sqlite3_prepare_v2(contactDB, update_stmt, -1, &statement, NULL);
        if (sqlite3_step(statement) == SQLITE_DONE)
        {
            NSLog(@"sussess to update damage report");
        } else {
            NSLog(@"Failed to update damage report");
        }
        sqlite3_finalize(statement);
        sqlite3_close(contactDB);
    }
}


// get lastest newreport id
+(NSString *) getLastestNewreportID:(NSString *) databasePath
{
    sqlite3 *contactDB;
    sqlite3_stmt    *statement;
    const char *dbpath = [databasePath UTF8String];
    
    int newreportID =0;
    int maxNewreportID = 0;
    if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK)
    {
        NSString *selectSQL = [NSString stringWithFormat:
                               @"SELECT id FROM tblNewreport"];
        
        const char *select_stmt = [selectSQL UTF8String];
        
        if( sqlite3_prepare_v2(contactDB, select_stmt, -1, &statement, NULL) == SQLITE_OK)
        {
            //NSLog(@"sussess to read");
            // Iterate over all returned rows
            while (sqlite3_step(statement) == SQLITE_ROW)
            {
                // Get newreportid
                newreportID = sqlite3_column_int(statement, 0);
                if (newreportID > maxNewreportID)
                {
                    maxNewreportID = newreportID;
                }
            }
        }
        else
        {
            NSLog(@"Failed to read");
        }
        sqlite3_finalize(statement);
        sqlite3_close(contactDB);
    }
    
    return [NSString stringWithFormat:@"%d",maxNewreportID];
}

// read all newreports
+(NSMutableArray *) readAllNewReports:(NSString *) databasePath
{
    NSMutableArray * NewreportDics = [[NSMutableArray alloc] initWithCapacity:0];
    
    sqlite3 *contactDB;
    sqlite3_stmt    *statement;
    const char *dbpath = [databasePath UTF8String];

    if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK)
    {
        NSString *selectSQL = [NSString stringWithFormat:
                               @"SELECT id, api, udid, wfid,wtgid,wtgvisid,blade,access,side FROM tblNewreport"];
        
        const char *select_stmt = [selectSQL UTF8String];
        
        if( sqlite3_prepare_v2(contactDB, select_stmt, -1, &statement, NULL) == SQLITE_OK)
        {
            //NSLog(@"sussess to read");
            // Iterate over all returned rows
            while (sqlite3_step(statement) == SQLITE_ROW)
            {
                // Get newreportid
                int newreportid = sqlite3_column_int(statement, 0);
                NSString * api = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 1)];
                NSString * udid = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 2)];
                NSString * wfid = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 3)];
                NSString * wtgid = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 4)];
                NSString * wtgvisid = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 5)];
                NSString * blade = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 6)];
                NSString * access = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 7)];
                NSString * side = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 8)];
                // add to arrays
                NSMutableDictionary * dic =[[NSMutableDictionary alloc] initWithCapacity:0];
                [dic setObject:[NSString stringWithFormat:@"%d" ,newreportid] forKey:@"id"];
                [dic setObject:udid forKey:@"udid"];
                [dic setObject:api forKey:@"api"];
                [dic setObject:wfid forKey:@"wfid"];
                [dic setObject:wtgid forKey:@"wtgid"];
                [dic setObject:wtgvisid forKey:@"wtgvisid"];
                [dic setObject:blade forKey:@"blade"];
                [dic setObject:access forKey:@"access"];
                [dic setObject:side forKey:@"side"];
                
                [NewreportDics addObject:dic];
            }
        }
        else
        {
            NSLog(@"Failed to read");
        }
        sqlite3_finalize(statement);
        sqlite3_close(contactDB);
    }
    
    return NewreportDics;
}

// read damage records by newreportid
+(NSMutableArray *) readAllDamageReportsByNewreportID:(NSString *)newreportid  onDatabase:(NSString*) databasePath
{
    
    NSMutableArray * DamageDics = [[NSMutableArray alloc] initWithCapacity:0];
    
    sqlite3 *contactDB;
    sqlite3_stmt    *statement;
    const char *dbpath = [databasePath UTF8String];
    
    if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK)
    {
        NSString *selectSQL = [NSString stringWithFormat:
                               @"SELECT distance, surface, damage, cause,length,width,area,depth,rating,notes,photo1,photo2,photo3,photo4 FROM tblDamageReport WHERE newreportid=%@",newreportid];
        
        const char *select_stmt = [selectSQL UTF8String];
        
        if( sqlite3_prepare_v2(contactDB, select_stmt, -1, &statement, NULL) == SQLITE_OK)
        {
            //NSLog(@"sussess to read");
            // Iterate over all returned rows
            while (sqlite3_step(statement) == SQLITE_ROW)
            {
                // Get damage record info
                NSString * distance = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 0)];
                NSString * surface = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 1)];
                NSString * damage = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 2)];
                NSString * cause = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 3)];
                NSString * length = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 4)];
                NSString * width = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 5)];
                NSString * area  = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 6)];
                NSString * depth = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 7)];
                NSString * rating = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 8)];
                NSString * notes = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 9)];
                NSString * photo1 = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 10)];
                NSString * photo2 = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 11)];
                NSString * photo3 = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 12)];
                NSString * photo4 = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 13)];
                
                // add to arrays
                NSMutableDictionary * dic =[[NSMutableDictionary alloc] initWithCapacity:0];
                [dic setObject:distance forKey:@"distance"];
                [dic setObject:surface forKey:@"surface"];
                [dic setObject:damage forKey:@"damage"];
                [dic setObject:cause forKey:@"cause"];
                [dic setObject:length forKey:@"length"];
                [dic setObject:width forKey:@"width"];
                [dic setObject:area forKey:@"area"];
                [dic setObject:depth forKey:@"depth"];
                [dic setObject:rating forKey:@"rating"];
                [dic setObject:notes forKey:@"notes"];
                [dic setObject:photo1 forKey:@"photo1"];
                [dic setObject:photo2 forKey:@"photo2"];
                [dic setObject:photo3 forKey:@"photo3"];
                [dic setObject:photo4 forKey:@"photo4"];
                
                [DamageDics addObject:dic];
            }
        }
        else
        {
            NSLog(@"Failed to read");
        }
        sqlite3_finalize(statement);
        sqlite3_close(contactDB);
    }
    
    return DamageDics;
}
// delete newreport by ID
+(void) deleteNewreportById:(NSString *) newreportid OnDB:(NSString *) databasePath
{
    
    sqlite3 *contactDB;
    sqlite3_stmt    *statement;
    const char *dbpath = [databasePath UTF8String];
    
    
    if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK)
    {
        NSString *deleteSQL = [NSString stringWithFormat:
                               @"DELETE FROM tblNewreport WHERE id =%@ ",newreportid];
        
        const char *delete_stmt = [deleteSQL UTF8String];
        
        sqlite3_prepare_v2(contactDB, delete_stmt, -1, &statement, NULL);
        if (sqlite3_step(statement) == SQLITE_DONE)
        {
            NSLog(@"sussess to detele newreport record");
        } else {
            NSLog(@"Failed to delete newreport record");
        }
        sqlite3_finalize(statement);
        sqlite3_close(contactDB);
    }
}

// delete damage reports by newreport id
+(void) deleteDamageReportByNewreportId:(NSString *) newreportid OnDB:(NSString *) databasePath
{
    sqlite3 *contactDB;
    sqlite3_stmt    *statement;
    const char *dbpath = [databasePath UTF8String];
    
    
    if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK)
    {
        NSString *deleteSQL = [NSString stringWithFormat:
                               @"DELETE FROM tblDamageReport WHERE newreportid =%@ ",newreportid];
        
        const char *delete_stmt = [deleteSQL UTF8String];
        
        sqlite3_prepare_v2(contactDB, delete_stmt, -1, &statement, NULL);
        if (sqlite3_step(statement) == SQLITE_DONE)
        {
            NSLog(@"sussess to detele damage records");
        } else {
            NSLog(@"Failed to delete damage records");
        }
        sqlite3_finalize(statement);
        sqlite3_close(contactDB);
    }
}

@end
